#!/bin/env python
#-*- coding:utf-8 -*-
author='将军'

from numpy import array
import os
import pymysql
import pandas
import jieba
#'如果是windows系统可以不用下面2行的方法'
import matplotlib as mat
mat.use('Agg')      
import matplotlib.pylab as pyl
from wordcloud import WordCloud

def connSQL(host,user,passwd,db):
    #'链接数据库,host="主机名",user="用户",passwd="密码",db="库名"'
    conn=pymysql.connect(host=host,user=user,passwd=passwd,db=db)
    return conn

def getData(sql,conn):
    #'获取评论数量,sql="sql查询语句",conn="数据库链接对象"'
    data=pandas.read_sql(sql,conn)
    return data
    
def wordCloudImage(name,data):
    #'绘制词云图'
    font=os.path.join(os.path.dirname(__file__),'DroidSansFallbackFull.ttf')    #'指定中文插件路径'
    word_cloud=WordCloud(collocations=False,font_path=font).generate(data)
    pyl.title('hexun')
    pyl.imshow(word_cloud)
    image='/tmp/download/%s.png' %name
    pyl.show()
    pyl.savefig(image)
    print('词云图片绘制已经完成,已经保存在如下位置:',image)
    pyl.close()

def commentHitsImage(name,comment,hits):
    #'绘制评论数和点击量散点图'
    pyl.title('hexun')
    pyl.xlabel('comment')
    pyl.ylabel('hits')
    pyl.plot(comment,hits,'o')
    pyl.show()
    image='/tmp/download/%s.png' %name
    pyl.savefig(image)
    print('散点图绘制已经完成,已经保存在如下位置:',image)
    pyl.close()

def abnormalData(comment,hits):
    #'根据散点图查找异常数据'
    abnormal_comment=[]
    abnormal_hits=[] 
    for i in comment:
        if i>500:   #'点击量大于40000的属于异常数据'
            abnormal_comment.append(i)
    for i in hits:
        if i >40000:    #'评论数大于500的属于异常数据'
            abnormal_hits.append(i)
    print('异常数据提取已完成')
    return (abnormal_hits,abnormal_comment)

def processAbnormal(comment,hits):
    #'处理异常值'
    for c in comment:
        if c>500:   #'评论数大于500的异常数据修改为标准差'
            comment[comment.index(c)]=34
    for h in hits:
        if h>40000:
            hits[hits.index(h)]=2814    #'点击量大于40000的异常数据修改为标准差'
    print('异常数据处理完成!')
    return (comment,hits)

def commentHistogram(name,comment):
    #'评论数直方图'
    font=os.path.join(os.path.dirname(__file__),'DroidSansFallbackFull.ttf')        #'指定中文字体'
    myfont=pyl.mpl.font_manager.FontProperties(fname=font)
    pyl.title('评论数直方图',fontproperties=myfont)
    pyl.xlabel('评论数量',fontproperties=myfont)
    pyl.ylabel('评论数总数:%s' %len(comment),fontproperties=myfont)
    pyl.hist(comment,10)
    pyl.show()
    image='/tmp/download/%s.png' %name
    pyl.savefig(image)
    print('评论数直方图绘制已完成.已经保存在如下位置:',image)
    pyl.close()

def hitsHistogram(name,hits):
    #'点击量直方图'
    font=os.path.join(os.path.dirname(__file__),'DroidSansFallbackFull.ttf')    #'下面2行都是指定中文字体'
    myfont=pyl.mpl.font_manager.FontProperties(fname=font)
    pyl.title('点击量直方图',fontproperties=myfont)
    pyl.xlabel('点击量',fontproperties=myfont)
    pyl.ylabel('点击量总数:%s' %len(hits),fontproperties=myfont)
    pyl.hist(hits,10)
    pyl.show()
    image='/tmp/download/%s.png' %name
    pyl.savefig(image)
    print('点数量直方图绘制完成,已经保存在如下位置:',image)

def main():
    mysql=['localhost','root','123456','hexun']
    conn=connSQL(mysql[0],mysql[1],mysql[2],mysql[3])
    sql='select * from myhexun'
    data=getData(sql,conn)
    titles=' '.join(data.T.values[1])
    comment=data.T.values[4]
    print('评论数:',comment)
    hits=data.T.values[3]
    print('点击数:',hits)
    print('正在提取标题中的关键词语,请稍后...')
    cut_titles=jieba.cut(titles)
    words=""
    for i in cut_titles:
        words=words+" "+str(i)
    print('标题关键词语提取完成,开始绘制hexun标题关键字的词云图,请稍后...')
    wordCloudImage('hot_title',words)
    print('开始绘制评论数和点击量的散点图,请稍后...')
    commentHitsImage('com_hit',comment,hits)
    print('正在查找异常评论和异常点击量,请稍后...')
    abnormal_data=abnormalData(comment,hits)
    print('异常评论:%s' %abnormal_data[1])
    print('异常点击量:%s' %abnormal_data[0])
    print('正在处理异常数据,请稍后...')
    normal_data=processAbnormal(list(comment),list(hits))
    print('开始绘制评论数直方图...')
    commentHistogram('comment_hist',array(normal_data[0]))
    print('开始绘制点击量直方图...')
    hitsHistogram('hists_hist',array(normal_data[1]))


if __name__=="__main__":
    main()
